package cn.mydsl.restful

import io.vertx.core.http.HttpServerRequest

/**
 * where之后的筛选条件
 * 对所有字段使用 `field` 包裹,且field内不含`向右点字符，防止注入
 * Created by xuybin on 17-2-6.
 */
data class Filter(val fields: List<FilterField>, val operat: Operat) {
    fun sql(): String {
        var sqlValue: String = ""
        if (this.fields.size >= 1 && this.fields[0].value == null) {
            when (this.operat) {
            //cmp[field|gt,lt,gteq,lteq,eq,!eq|field]，组成一个筛选条件
                Operat.LT, Operat.LT_EQUAL, Operat.GT, Operat.GT_EQUAL, Operat.EQUAL, Operat.NOT_EQUAL ->
                if(this.fields.size ==2){
                    //字段信息，是服务端获取，不会为空
                    sqlValue += "${this.fields[0].name} ${this.operat.sqlOperat} ${this.fields[1].name}"
                }
                Operat.DESC, Operat.ASC ->{
                    //ord[desc[field|field]],使用,连接起来
                    for (field in this.fields) {
                        if(field.jointRelate!=JointRelate.NONE){
                            //字段信息，是服务端获取，不会为空
                            sqlValue += "${field.name} ${this.operat.sqlOperat},"
                        }
                    }
                    sqlValue = sqlValue.substringBeforeLast(",")
                }
                else->sqlValue+=""
            }

        } else if (this.fields.size >= 1) {
            //同一个s[operat[field1|field2]]=urlStr|operat 内,d 关系只能是OR
            var count=0
            for (field in this.fields) {
                if (field.jointRelate!=JointRelate.NONE && field.sql(operat).isNotEmpty()) {
                    count++
                    sqlValue += "${field.sql(operat)} OR "
                }
            }
            if (sqlValue.isNotEmpty()) {
                sqlValue=sqlValue.substringBeforeLast("OR")
                if(count>1){
                    sqlValue = "(${sqlValue})"
                }
            }
        }
        return sqlValue
    }
}

//Map<String,Boolean> KEY:tableName VALUE:isRelated(是否手动关联) cmp[field1|eq|field2]
fun List<Filter>.sqlNeedTableNames(currentTableName:String):Map<String,JointRelate>{
    var needTableNameMap= mutableMapOf<String,JointRelate>()

    for(fiter in this){
        //满足cmp[field1|eq|field2]
        if(fiter.fields.size==2 && fiter.fields[0].value==null && fiter.operat==Operat.EQUAL) {
            //特殊标记一下，手动指定关联关系的表
            var tableName=fiter.fields[0].tableName
            if(tableName!=currentTableName){
                if(needTableNameMap.containsKey(tableName)){
                    needTableNameMap[tableName]=JointRelate.LINKED
                }else{
                    needTableNameMap.put(tableName,JointRelate.LINKED)
                }
                //fiter.fields[0].jointRelate=JointRelate.LINKED
            }
            tableName=fiter.fields[1].tableName
            if(tableName!=currentTableName){
                if(needTableNameMap.containsKey(tableName)){
                    needTableNameMap[tableName]=JointRelate.LINKED
                }else{
                    needTableNameMap.put(tableName,JointRelate.LINKED)
                }
                //fiter.fields[1].jointRelate=JointRelate.LINKED
            }
        }else{
            for(field in fiter.fields){
                var tableName=field.tableName
                if(tableName!=currentTableName){
                    if(needTableNameMap.containsKey(tableName)){
                        needTableNameMap[tableName]=field.jointRelate
                    }else{
                        needTableNameMap.put(tableName,field.jointRelate)
                    }
                }
            }
        }
    }
    //排除未关联的表
    var  needTableNameMap2=needTableNameMap.filter { n->n.value!= JointRelate.NONE}

    for(fiter in this){
        for(field in fiter.fields){
            var need=needTableNameMap2[field.tableName]
            if(need!=null && field.jointRelate== JointRelate.NONE){
                field.jointRelate=need //重新设置关系，补充 JointRelate.LINKED 类型
            }
        }
    }
    return needTableNameMap2
}


//拼接WHERE之后的sql语句
fun List<Filter>.sqlAfterWhere():String{
    var sqlValue: String = ""

    if (this.size>0) {
        //提取fmt[([0]_or_[1])_and_[2])_order_by_[4],[3]] 内的字符串 _已经变为空格
        var formatFilter = this.firstOrNull({ s -> s.operat == Operat.FORMAT })
        if (formatFilter != null && formatFilter.fields.size > 0) {
            var value1 = formatFilter.fields[0].value
            if (value1 != null && value1.size > 0) {
                sqlValue = value1[0]
            }
        }

        if (sqlValue.isNotEmpty()) {
            //提出到字符串，则以此组织关系
            for (i in this.indices) {
                sqlValue = sqlValue.replace("[$i]", "${this[i].sql()}")
            }
            if ("\\[\\d+\\]".toRegex().matches(sqlValue)) {
                throw Exception("还有未格式化的sql：${sqlValue}")
            } else if (sqlValue.trim().toLowerCase().contains(" order ", true)) {
                //有where条件时，追加WHERE关键字,最外层加括号包裹，利于外面拼装
                sqlValue="("+sqlValue.trim().toLowerCase().replace(" order ",") order ")
            }else{
                //有where条件时，追加WHERE关键字,最外层加括号包裹，利于外面拼装
                sqlValue = "( ${sqlValue} )"
            }
        } else {
            //否则，默认以AND组织
            var sqlOrderby: String = ""
            for (filter in this) {
                if(filter.operat==Operat.INCL || filter.operat==Operat.EXCL){
                    continue
                }else if (filter.operat != Operat.ASC && filter.operat != Operat.DESC) {
                    var addsql=filter.sql()
                    if(addsql.isNotEmpty()){
                        sqlValue += "${addsql} AND "
                    }
                } else {
                    var addsql=filter.sql()
                    if(addsql.isNotEmpty()){
                        sqlOrderby += "${filter.sql()},"
                    }
                }
            }
            if (sqlValue.isNotEmpty()) {
                sqlValue = sqlValue.substringBeforeLast("AND")
                if (sqlOrderby.isNotEmpty()) {
                    sqlValue = "( ${sqlValue} ) ORDER BY ${sqlOrderby.substringBeforeLast(",")}"
                } else {
                    sqlValue = "( ${sqlValue} )"
                }
            } else if (sqlOrderby.isNotEmpty()) {
                sqlValue = " ORDER BY ${sqlOrderby.substringBeforeLast(",")}"
            }
        }
    }
    return sqlValue
}
/**
 * 提取筛选条件的扩展方法
 * tableFieldMap的key,需统一为Table.Field或DB.Table.Field,可直接使用Field.sqlField
 * Created by xuybin on 17-2-6.
 */
fun HttpServerRequest.fiters(defaultDbName: String,currentTable:String,tableFieldMap: Map<String, Field>): List<Filter> {
    val fiters = mutableListOf<Filter>()
    val rgex1 = "s\\[([^` \\[\\]]+)\\[([^` \\[\\]]+)\\]\\]".toRegex()
    val rgex2 = "cmp\\[([^` \\[\\],]+),([!eqlgt]+),([^` \\[\\],]+)\\]".toRegex()
    val rgex3 = "ord\\[(asc|desc|ASC|DESC)\\[([^` \\[\\]]+)\\]\\]".toRegex()
    val rgex4 ="fmt\\[(([_,\\(\\)\\[\\]0-9]|or|and|order|by|OR|AND|ORDER|BY)+)\\]".toRegex()
    val rgex5 ="flt\\[(incl|excl|INCL|EXCL)\\[([^` \\[\\]]+)\\]\\]".toRegex()

    //-------------------------------------------------------------------------
    //检查筛选条件
    for (it in this.params()) {

        //-------------------------------------------------------------------------
        //字段值筛选
        var groups = rgex1.matchEntire(it.key)?.groups
        if (groups != null) {

            var gfield = groups[2]
            var goperat = groups[1]

            if (goperat != null && gfield != null && it.value != null && it.value.isNotEmpty()) {

                var name = gfield.value.split("|")
                var value = it.value.split("|")
                //以服务器的字段和字段类型为准
                var fields = mutableListOf<FilterField>()
                var j: Int = 0
                for (i in name.indices) {
                    //多个字段可以只有一个值，或者 各有一个值
                    if (name.size == value.size) {
                        j = i
                    }
                    var filterField = tableFieldMap.find(defaultDbName,currentTable, name[i])
                    if (filterField != null) {
                        if (goperat.value == Operat.EQUAL.urlStr || goperat.value == Operat.NOT_EQUAL.urlStr || goperat.value == Operat.LIKE.urlStr) {
                            //相等操作 和like 操作，只有一个value
                            filterField.value=listOf(value[j])
                            fields.add(filterField)
                        } else if (goperat.value == Operat.RANGE.urlStr) {
                            if(value[j].split(",").size >= 2){
                                filterField.value= value[j].split(",").take(2)
                            }else if(value[j].split(",").size ==1){
                                filterField.value= listOf(value[j],"")
                            }else{
                                continue
                            }
                            fields.add(filterField)
                        } else if (goperat.value == Operat.NOT_IN.urlStr || goperat.value == Operat.IN.urlStr) {
                            filterField.value= value[j].split(",")
                            fields.add(filterField)
                        }
                    }
                }
                if (fields.size > 0) {
                    //根据筛选操作，追加筛选列表
                    when (goperat.value.toLowerCase()) {
                        Operat.LIKE.urlStr -> fiters.add(Filter(fields.toSet().toList(), Operat.LIKE))
                        Operat.RANGE.urlStr -> fiters.add(Filter(fields.toList(), Operat.RANGE))
                        Operat.EQUAL.urlStr -> fiters.add(Filter(fields.toSet().toList(), Operat.EQUAL))
                        Operat.NOT_EQUAL.urlStr -> fiters.add(Filter(fields.toSet().toList(), Operat.NOT_EQUAL))
                        Operat.IN.urlStr -> fiters.add(Filter(fields.toSet().toList(), Operat.IN))
                        Operat.NOT_IN.urlStr -> fiters.add(Filter(fields.toSet().toList(), Operat.NOT_IN))
                        else -> println("Operat erro:${it.key}")
                    }
                } else {
                    println("All field not find:${it.key}=${it.value} ----> ${tableFieldMap}")
                }
            }
            continue
        }//字段值筛选

        //-------------------------------------------------------------------------
        //参与排序的字段
        groups = rgex3.matchEntire(it.key)?.groups
        if (groups != null) {
            var gfield = groups[2]
            var goperat = groups[1]
            if (gfield != null && goperat != null) {
                var name = gfield.value.split("|")
                //以服务器的字段和字段类型为准
                var fields = mutableSetOf<FilterField>() //去掉重复的，顺序无关
                for (i in name.indices) {
                    var filterField=tableFieldMap.find(defaultDbName,currentTable, name[i])
                    if (filterField != null) {
                        fields.add(filterField)
                    }
                }
                if (fields.size > 0) {
                    when (goperat.value.toLowerCase()) {
                        Operat.ASC.urlStr -> fiters.add(Filter(fields.toList(), Operat.ASC))
                        Operat.DESC.urlStr -> fiters.add(Filter(fields.toList(), Operat.DESC))
                        else -> println("Operat erro:${it.key}")
                    }
                } else {
                    println("All field not find:${it.key}=${it.value} ----> ${tableFieldMap}")
                }
            }
            continue
        }//参与排序的字段

        //-------------------------------------------------------------------------
        //where筛选条件位置,格式化
        groups = rgex4.matchEntire(it.key)?.groups
        if (groups != null) {
            var gFormat = groups[1]
            if (gFormat != null) {
                fiters.add(Filter(listOf(FilterField("", FieldType.FORMAT,JointRelate.NONE, gFormat.value.replace("_"," ").split('='))), Operat.FORMAT))
            }
            continue
        }

        //-------------------------------------------------------------------------
        //纯字段比较
        groups = rgex2.matchEntire(it.key)?.groups
        if (groups != null) {
            var gfield = groups[1]
            var gfield2 = groups[3]
            var goperat = groups[2]
            if (gfield != null && goperat != null && gfield2 != null) {
                //所有字段以向左点`包裹，对于字段数据里的左点`，需全部替换为空，即可防止注入
                var name1 = gfield.value
                var name2 = gfield2.value
                //以服务器的字段和字段类型为准
                var fields = mutableListOf<FilterField>()
                //第一个字段
                var filterField= tableFieldMap.find(defaultDbName,currentTable, name1)
                if (filterField != null) {
                    fields.add(filterField)
                }
                //第二个字段
                filterField = tableFieldMap.find(defaultDbName,currentTable, name2)
                if (filterField != null) {
                    fields.add(filterField)
                }

                if (fields.size > 0) {
                    when (goperat.value.toLowerCase()) {
                        Operat.LT.urlStr -> fiters.add(Filter(fields, Operat.LT))
                        Operat.LT_EQUAL.urlStr -> fiters.add(Filter(fields, Operat.LT_EQUAL))
                        Operat.GT.urlStr -> fiters.add(Filter(fields, Operat.GT))
                        Operat.GT_EQUAL.urlStr -> fiters.add(Filter(fields, Operat.GT_EQUAL))
                        Operat.EQUAL.urlStr -> fiters.add(Filter(fields, Operat.EQUAL))
                        Operat.NOT_EQUAL.urlStr -> fiters.add(Filter(fields, Operat.NOT_EQUAL))
                        "<>" -> fiters.add(Filter(fields, Operat.NOT_EQUAL))
                        else -> println("Operat erro:${it.key}")
                    }
                } else {
                    println("All field not find:${it.key}=${it.value} ----> ${tableFieldMap}")
                }
            }
            continue
        }

        //手动指定字段或排除字段
        groups = rgex5.matchEntire(it.key)?.groups
        if (groups != null) {
            var gfield = groups[2]
            var goperat = groups[1]
            if (gfield != null && goperat != null) {
                var name = gfield.value.split("|")
                //以服务器的字段和字段类型为准
                var fields = mutableSetOf<FilterField>() //去掉重复的，顺序无关
                for (i in name.indices) {
                    var filterField=tableFieldMap.find(defaultDbName,currentTable, name[i])
                    if (filterField != null) {
                        fields.add(filterField)
                    }
                }
                if (fields.size > 0) {
                    when (goperat.value.toLowerCase()) {
                        Operat.EXCL.urlStr -> fiters.add(Filter(fields.toList(), Operat.EXCL))
                        Operat.INCL.urlStr -> fiters.add(Filter(fields.toList(), Operat.INCL))
                        else -> println("Operat erro:${it.key}")
                    }
                } else {
                    println("All field not find:${it.key}=${it.value} ----> ${tableFieldMap}")
                }
            }
            continue
        }//手动指定字段或排除字段

    }//检查筛选条件
    return fiters.toList()
}